﻿using UnityEngine;
using System.Collections;
using System;
using System.Data;
using MySql.Data.MySqlClient;
using MySql.Data;

public class MySqlAccess
{
    public static MySqlConnection dbConnection;
    private static string host = "localhost";
    private static string id = "root";
    private static string password = "";
    private static string database = "test";

    public MySqlAccess()
    {
        OpenDB();
    }

    public static void OpenDB()
    {
        try
        {
            string connectionString = string.Format("Server = {0};port = {4};Database = {1}; User ID = {2}; Password = {3}", host,
                database,
                id,password, "3306");
            dbConnection = new MySqlConnection(connectionString);
            dbConnection.Open();
            Debug.Log(dbConnection.State);
        }
        catch (Exception ee)
        {
            throw new Exception("服务器连接失败，请重新检查是否打开MySql服务" + ee.ToString());
        }
    }

    public DataSet CreateTable(string name, string[] col, string[] colType)
    {
        if (col.Length != colType.Length)
        {
            throw new Exception("columns.Length != colType.Length");
        }
        string query = "CREATE TABLE " + name + "(" + col[0] + " " + colType[0];
        for (int i = 1; i < col.Length; i++)
        {
            query += "," + col[i] + " " + col[i];
        }
        query += ")";
        return ExecuteQuery(query);
    }

    public DataSet CreateTableAutoID(string name, string[] col, string[] colType)
    {
        if (col.Length != colType.Length)
        {
            throw new Exception("columns.Length != colType.Length");
        }
        string query = "CREATE TABLE " + name + "(" + col[0] + " " + colType[0] + " NOT NULL AUTO_INCREMENT";
        for (int i = 1; i < col.Length; i++)
        {
            query += "," + col[i] + " " + colType[i];
        }
        query += ", PRIMARY KEY (" + col[0] + ")" + ")";
        Debug.Log(query);

        return ExecuteQuery(query);
    }

    public DataSet InsertInto(string tableName, string[] col, string[] values)
    {
        if (col.Length != values.Length)
        {
            throw new Exception("values.Length != colType.Length");
        }
        string query = "INSERT INTO " + tableName + " (" + col[0];
        for (int i = 1; i < col.Length; i++)
        {
            query += ", " + col[i];
        }
        query += ") VALUES (" + "'" + values[0] + "'";
        for (int j = 1; j < values.Length; j++)
        {
            query += ", " + "'" + values[j] + "'";
        }
        query += ")";
        Debug.Log(query);
        return ExecuteQuery(query);
    }

    public DataSet SelectWhere(string tableName, string[] items, string[] col, string[] operation, string[] values)
    {
        if (col.Length != operation.Length || operation.Length != values.Length)
        {
            throw new Exception("col.Length != operation.Length != values.Length");
        }

        string query = "SELECT " + items[0];
        for (int i = 1; i < items.Length; i++)
        {
            query += ", " + items[i];
        }
        query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'"+ values[0] + "'";
        for (int j = 1; j < col.Length; j++)
        {
            query += " AND " + col[j] + operation[j] + "'" + values[j] + "'";
        }
        Debug.Log(query);
        return ExecuteQuery(query);
    }

    public DataSet UpdateSet(string tableName, string[] cols, string[] colsValues, string selectKey, string selectKeyValue)
    {
        if (cols.Length != colsValues.Length)
        {
            throw new Exception("cols.Length != colsValues.Length");
        }
        string query = "UPDATE " + tableName + " SET " + cols[0] + "=" + colsValues[0];
        for (int i = 1; i < colsValues.Length; i++)
        {
            query += ", " + cols[i] + " = " + colsValues[i];
        }
        query += " WHERE " + selectKey + " = " + selectKeyValue;
        Debug.Log(query);
        return ExecuteQuery(query);
    }

    public DataSet Delete(string tableName, string[] cols, string[] colsValues)
    {
        if (cols.Length != colsValues.Length)
        {
            throw new Exception("cols.Length != colsValues.Length");
        }
        string query = "DELETE FROM " + tableName + " WHERE " + cols[0] + " = " + colsValues[0];
        for (int i = 0; i < cols.Length; i++)
        {
            query += " OR " + cols[i] + " = " + colsValues[i];
        }
        Debug.Log(query);
        return ExecuteQuery(query);
    }

    public void Close()
    {
        if (dbConnection != null)
        {
            dbConnection.Close();
            dbConnection.Dispose();
            dbConnection = null;
        }
    }
    public static DataSet ExecuteQuery(string sqlString)
    {
        if (dbConnection.State == ConnectionState.Open)
        {
            DataSet ds = new DataSet();
            try
            {
                MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection);
                da.Fill(ds);
            }
            catch (Exception ee)
            {
                throw new Exception("SQL:" + sqlString + "/n" + ee.Message.ToString());
            }
            return ds;
        }
        return null;
    }

}
